from pathlib import Path
import pandas as pd
from tabulate import tabulate  # 需要安装：pip install tabulate
from data_loader import EduDataLoader


def interactive_student_query():
    """
    增强版学生信息查询系统（适配清洗后数据结构）
    功能：
    - 显示学生完整档案
    - 详细考勤记录（含类型统计）
    - 消费分析（按月份汇总）
    - 成绩趋势分析
    - 任课教师信息
    """
    # 初始化数据加载器
    try:
        print("⏳ 正在加载数据...")
        loader = EduDataLoader()
        data = loader.load_all()
        print("✅ 数据加载完成")
        print(f"学生总数: {len(data['students'])} | 教师数: {len(data['teachers'])}")

        # 显示每个文件的前5行内容
        print("\n📄 各文件前5行数据预览:")
        for key, df in data.items():
            print(f"\n=== {key.upper()} ===")
            print(tabulate(df.head(), headers='keys', tablefmt='psql'))

    except Exception as e:
        print(f"❌ 数据加载失败: {str(e)}")
        return

    while True:
        print("\n" + "=" * 50)
        print("🎓 学生综合信息查询系统（输入q退出）")
        print("=" * 50)

        student_id = input("请输入要查询的学生学号: ").strip()
        if student_id.lower() == 'q':
            break

        try:
            student_id = int(student_id)  # 转换为整数匹配学号
        except ValueError:
            print("⚠️ 请输入有效的数字学号")
            continue

        # 获取完整学生数据
        student_data = get_comprehensive_student_data(data, student_id)

        # 显示信息
        display_comprehensive_info(student_data, data)


def get_comprehensive_student_data(data: dict, student_id: int) -> dict:
    """获取学生所有关联数据（适配清洗后结构）"""
    result = {
        'basic': None,
        'attendance': {'records': None, 'stats': None},
        'scores': {'records': None, 'stats': None, 'subjects': None},
        'consumption': {'records': None, 'monthly': None},
        'teachers': None,
        'exists': False
    }

    # 1. 基础信息查询
    students = data['students']
    target = students[students['bf_StudentID'] == student_id]

    if len(target) == 0:
        print(f"⚠️ 未找到学号为 {student_id} 的学生记录")
        return result

    result['exists'] = True
    result['basic'] = target.iloc[0].to_dict()
    class_name = result['basic'].get('cla_Name')

    # 2. 考勤数据（含统计）
    attendance = data['attendance']
    attend_records = attendance[attendance['bf_studentID'] == student_id]

    if not attend_records.empty:
        result['attendance']['records'] = attend_records.sort_values('datetime', ascending=False)
        result['attendance']['stats'] = attend_records['std_type'].value_counts().to_dict()

    # 3. 成绩数据（含科目分析）
    scores = data['scores']
    score_records = scores[scores['bf_studentID'] == student_id]

    if not score_records.empty:
        result['scores']['records'] = score_records.sort_values('exam_date', ascending=False)

        # 计算统计量（排除缺考记录）
        valid_scores = score_records[score_records['score_status'] == '正常']
        if not valid_scores.empty:
            result['scores']['stats'] = {
                'avg_score': valid_scores['score'].mean(),
                'max_score': valid_scores['score'].max(),
                'min_score': valid_scores['score'].min(),
                'exam_count': len(valid_scores)
            }
        result['scores']['subjects'] = score_records['subject'].unique()

    # 4. 消费数据（含月度汇总）
    consumption = data['consumption']
    consume_records = consumption[consumption['bf_StudentID'] == student_id]

    if not consume_records.empty:
        result['consumption']['records'] = consume_records.sort_values('datetime', ascending=False)

        # 按月消费统计
        monthly = consume_records.copy()
        monthly['month'] = monthly['datetime'].dt.to_period('M')
        result['consumption']['monthly'] = monthly.groupby('month')['amount'].sum()

    # 5. 任课教师信息
    if class_name:
        teachers = data['teachers']
        result['teachers'] = teachers[teachers['cla_Name'] == class_name]

    return result


def display_comprehensive_info(data: dict, all_data: dict):
    """显示增强版学生信息（适配清洗后结构）"""
    if not data['exists']:
        return

    student = data['basic']

    # ========== 基本信息 ==========
    print("\n" + "=" * 50)
    print(f"📚 学生档案 [学号: {student['bf_StudentID']}]".center(50))
    print("=" * 50)

    info_table = [
        ["姓名", student.get('bf_Name', 'N/A')],
        ["性别", student.get('bf_sex', 'N/A')],
        ["年龄", f"{student.get('age', 'N/A')}岁"],
        ["班级", student.get('cla_Name', 'N/A')],
        ["政治面貌", student.get('policy_code', 'N/A')],
        ["住宿情况", student.get('dorm_status', 'N/A')],
        ["是否本地", "是" if student.get('is_local') == 1 else "否"]
    ]
    print(tabulate(info_table, tablefmt="grid"))

    # ========== 任课教师 ==========
    if data['teachers'] is not None and not data['teachers'].empty:
        print("\n👨‍🏫 任课教师:")
        print(tabulate(
            data['teachers'][['teacher_name', 'subject']],
            headers=["教师姓名", "任教科目"],
            showindex=False,
            tablefmt="pretty"
        ))

    # ========== 考勤信息 ==========
    print("\n📅 考勤统计:")
    if data['attendance']['stats']:
        stats_table = [[k, v] for k, v in data['attendance']['stats'].items()]
        print(tabulate(stats_table, headers=["类型", "次数"], tablefmt="grid"))

        print("\n最近5条记录:")
        print(tabulate(
            data['attendance']['records'][['datetime', 'std_type']].head(),
            headers=["时间", "类型"],
            showindex=False,
            tablefmt="psql"
        ))
    else:
        print("无考勤记录")

    # ========== 成绩信息 ==========
    print("\n📊 成绩分析:")
    if data['scores']['records'] is not None and not data['scores']['records'].empty:
        scores = data['scores']
        if scores['stats']:
            print(f"平均分: {scores['stats']['avg_score']:.1f} | "
                  f"最高分: {scores['stats']['max_score']} | "
                  f"最低分: {scores['stats']['min_score']} | "
                  f"考试次数: {scores['stats']['exam_count']}")

        if scores['subjects'] is not None and len(scores['subjects']) > 0:
            print("\n考试科目:", ", ".join(scores['subjects']))

        print("\n最近5次考试:")
        print(tabulate(
            scores['records'][['exam_date', 'score', 'subject', 'EXAM_KIND_NAME']].head(),
            headers=["日期", "分数", "科目", "考试类型"],
            showindex=False,
            tablefmt="psql"
        ))
    else:
        print("无成绩记录")

    # ========== 消费信息 ==========
    print("\n💵 消费情况:")
    if data['consumption']['records'] is not None and not data['consumption']['records'].empty:
        consume = data['consumption']
        print(f"总消费次数: {len(consume['records'])}")
        print(f"总消费金额: {consume['records']['amount'].sum():.2f}元")

        if consume['monthly'] is not None:
            print("\n月度消费汇总:")
            print(tabulate(
                consume['monthly'].reset_index(),
                headers=["月份", "消费金额"],
                showindex=False,
                tablefmt="psql"
            ))

        print("\n最近5笔消费:")
        print(tabulate(
            consume['records'][['datetime', 'amount', 'period']].head(),
            headers=["时间", "金额", "时段"],
            showindex=False,
            tablefmt="psql"
        ))
    else:
        print("无消费记录")


if __name__ == "__main__":
    interactive_student_query()